Query is stuck

Re: Query is stuck

This is a multi-part message in MIME format.

------_=_NextPart_001_01CADB09.C55D6E88
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

controlsmartdb=3D# select * from pg_stat_activity where waiting=3D't';

ERROR: column "waiting" does not exist

=20

From: Plugge, Joe R. [mailto:JRPlugge@west.com]=20
Sent: Tuesday, April 13, 2010 6:32 PM
To: Satish Burnwal (sburnwal); pgsql-general@postgresql.org
Cc: pgsql-admin@postgresql.org
Subject: RE: Query is stuck

=20

What do you get when you run this?

=20

select * from pg_stat_activity where waiting=3D't';

=20

=20

=20

From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Satish Burnwal
(sburnwal)
Sent: Tuesday, April 13, 2010 7:58 AM
To: pgsql-general@postgresql.org
Cc: pgsql-admin@postgresql.org
Subject: [ADMIN] Query is stuck

=20

I have a query which is not giving me the result even after 30 minutes.
I want to know how to detect what is going and what's wrong ?=20

=20

EXPLAIN query - gives me the following:

controlsmartdb=3D# explain select report_id, dm_ip, dm_mac, dm_user,
dm_os, report_time, sys_name, sys_user, sys_user_domain, ss_key,
login_time, role_id, new_vlan_id from repcopy as a where report_time =3D
(select max(report_time) from repcopy as b where a.dm_ip =3D b.dm_ip and
a.dm_user=3Db.dm_user and b.ss_key !=3D '') and report_status =3D 0 and
dm_user =3D 'u1';

QUERY PLAN

------------------------------------------------------------ ------------
----------------------------------------------------

Seq Scan on repcopy a (cost=3D0.00..1630178118.35 rows=3D35 =
width=3D133)

Filter: ((report_status =3D 0) AND ((dm_user)::text =3D 'u1'::text) =
AND
(report_time =3D (subplan)))

SubPlan

-> Aggregate (cost=3D8151.65..8151.66 rows=3D1 width=3D8)

-> Seq Scan on repcopy b (cost=3D0.00..8151.65 rows=3D1
width=3D8)

Filter: ((($0)::text =3D (dm_ip)::text) AND (($1)::text =
=3D
(dm_user)::text) AND ((ss_key)::text <> ''::text))

(6 rows)

=20

But EXPLAIN ANALYSE query hangs (is not giving me any output even after
30 minutes).

=20

Pg_stat_activity shows this - SELECT procpid, usename, current_query,
query_start from pg_stat_activity:

2942 | postgres | select report_id, dm_ip, dm_mac, dm_user, dm_os,
report_time, sys_name, sys_user, sys_user_domain, ss_key, login_time,
role_id, new_vlan_id from repcopy as a where report_time =3D (select
max(report_time) from repcopy as b where a.dm_ip =3D b.dm_ip and
a.dm_user=3Db.dm_user and b.ss_key !=3D '') and report_status =3D 0 and
dm_user =3D 'u1'; | 2010-04-13 18:20:02.828623+05:30

=20

=20

In such a case what can I do ?=20

=20


------_=_NextPart_001_01CADB09.C55D6E88
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

xmlns:o=3D"urn:schemas-microsoft-com:office:office" =
xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:x=3D"urn:schemas-microsoft-com:office:excel" =
xmlns:p=3D"urn:schemas-microsoft-com:office:powerpoint" =
xmlns:a=3D"urn:schemas-microsoft-com:office:access" =
xmlns:dt=3D"uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" =
xmlns:s=3D"uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" =
xmlns:rs=3D"urn:schemas-microsoft-com:rowset" xmlns:z=3D"#RowsetSchema" =
xmlns:b=3D"urn:schemas-microsoft-com:office:publisher" =
xmlns:ss=3D"urn:schemas-microsoft-com:office:spreadsheet" =
xmlns:c=3D"urn:schemas-microsoft-com:office:component:spread sheet" =
xmlns:odc=3D"urn:schemas-microsoft-com:office:odc" =
xmlns:oa=3D"urn:schemas-microsoft-com:office:activation" =
xmlns:html=3D"http://www.w3.org/TR/REC-html40" =
xmlns:q=3D"http://schemas.xmlsoap.org/soap/envelope/" =
xmlns:rtc=3D"http://microsoft.com/officenet/conferencing" =
xmlns:D=3D"DAV:" xmlns:Repl=3D"http://schemas.microsoft.com/repl/" =
xmlns:mt=3D"http://schemas.microsoft.com/sharepoint/soap/mee tings/" =
xmlns:x2=3D"http://schemas.microsoft.com/office/excel/2003/x ml" =
xmlns:ppda=3D"http://www.passport.com/NameSpace.xsd" =
xmlns:ois=3D"http://schemas.microsoft.com/sharepoint/soap/oi s/" =
xmlns:dir=3D"http://schemas.microsoft.com/sharepoint/soap/di rectory/" =
xmlns:ds=3D"http://www.w3.org/2000/09/xmldsig#" =
xmlns:dsp=3D"http://schemas.microsoft.com/sharepoint/dsp" =
xmlns:udc=3D"http://schemas.microsoft.com/data/udc" =
xmlns:xsd=3D"http://www.w3.org/2001/XMLSchema" =
xmlns:sub=3D"http://schemas.microsoft.com/sharepoint/soap/20 02/1/alerts/"=
xmlns:ec=3D"http://www.w3.org/2001/04/xmlenc#" =
xmlns:sp=3D"http://schemas.microsoft.com/sharepoint/" =
xmlns:sps=3D"http://schemas.microsoft.com/sharepoint/soap/" =
xmlns:xsi=3D"http://www.w3.org/2001/XMLSchema-instance" =
xmlns:udcs=3D"http://schemas.microsoft.com/data/udc/soap" =
xmlns:udcxf=3D"http://schemas.microsoft.com/data/udc/xmlfile " =
xmlns:udcp2p=3D"http://schemas.microsoft.com/data/udc/partto part" =
xmlns:wf=3D"http://schemas.microsoft.com/sharepoint/soap/wor kflow/" =
xmlns:dsss=3D"http://schemas.microsoft.com/office/2006/digsi g-setup" =
xmlns:dssi=3D"http://schemas.microsoft.com/office/2006/digsi g" =
xmlns:mdssi=3D"http://schemas.openxmlformats.org/package/200 6/digital-sig=
nature" =
xmlns:mver=3D"http://schemas.openxmlformats.org/markup-compa tibility/2006=
" xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" =
xmlns:mrels=3D"http://schemas.openxmlformats.org/package/200 6/relationshi=
ps" xmlns:spwp=3D"http://microsoft.com/sharepoint/webpartpages" =
xmlns:ex12t=3D"http://schemas.microsoft.com/exchange/service s/2006/types"=
=
xmlns:ex12m=3D"http://schemas.microsoft.com/exchange/service s/2006/messag=
es" =
xmlns:pptsl=3D"http://schemas.microsoft.com/sharepoint/soap/ SlideLibrary/=
" =
xmlns:spsl=3D"http://microsoft.com/webservices/SharePointPor talServer/Pub=
lishedLinksService" xmlns:Z=3D"urn:schemas-microsoft-com:" =
xmlns:st=3D"
" xmlns=3D"http://www.w3.org/TR/REC-html40">


charset=3Dus-ascii">

Re: Query is stuck

--_000_BD69807DAE0CE44CA00A8338D0FDD08302CF97EA35oma00cexmbx 03_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

What version of postgres are you on?

From: Satish Burnwal (sburnwal) [mailto:sburnwal@cisco.com]
Sent: Tuesday, April 13, 2010 8:04 AM
To: Plugge, Joe R.; pgsql-general@postgresql.org
Cc: pgsql-admin@postgresql.org
Subject: RE: Query is stuck

controlsmartdb=3D# select * from pg_stat_activity where waiting=3D't';
ERROR: column "waiting" does not exist

From: Plugge, Joe R. [mailto:JRPlugge@west.com]
Sent: Tuesday, April 13, 2010 6:32 PM
To: Satish Burnwal (sburnwal); pgsql-general@postgresql.org
Cc: pgsql-admin@postgresql.org
Subject: RE: Query is stuck

What do you get when you run this?

select * from pg_stat_activity where waiting=3D't';



From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql=
..org] On Behalf Of Satish Burnwal (sburnwal)
Sent: Tuesday, April 13, 2010 7:58 AM
To: pgsql-general@postgresql.org
Cc: pgsql-admin@postgresql.org
Subject: [ADMIN] Query is stuck

I have a query which is not giving me the result even after 30 minutes. I w=
ant to know how to detect what is going and what's wrong ?

EXPLAIN query - gives me the following:
controlsmartdb=3D# explain select report_id, dm_ip, dm_mac, dm_user, dm_os,=
report_time, sys_name, sys_user, sys_user_domain, ss_key, login_time, rol=
e_id, new_vlan_id from repcopy as a where report_time =3D (select max(repor=
t_time) from repcopy as b where a.dm_ip =3D b.dm_ip and a.dm_user=3Db.dm_us=
er and b.ss_key !=3D '') and report_status =3D 0 and dm_user =3D 'u1';
QUERY PLAN
------------------------------------------------------------ ---------------=
-------------------------------------------------
Seq Scan on repcopy a (cost=3D0.00..1630178118.35 rows=3D35 width=3D133)
Filter: ((report_status =3D 0) AND ((dm_user)::text =3D 'u1'::text) AND =
(report_time =3D (subplan)))
SubPlan
-> Aggregate (cost=3D8151.65..8151.66 rows=3D1 width=3D8)
-> Seq Scan on repcopy b (cost=3D0.00..8151.65 rows=3D1 width=
=3D8)
Filter: ((($0)::text =3D (dm_ip)::text) AND (($1)::text =
=3D (dm_user)::text) AND ((ss_key)::text <> ''::text))
(6 rows)

But EXPLAIN ANALYSE query hangs (is not giving me any output even after 30 =
minutes).

Pg_stat_activity shows this - SELECT procpid, usename, current_query, query=
_start from pg_stat_activity:
2942 | postgres | select report_id, dm_ip, dm_mac, dm_user, dm_os, report_t=
ime, sys_name, sys_user, sys_user_domain, ss_key, login_time, role_id, new=
_vlan_id from repcopy as a where report_time =3D (select max(report_time) f=
rom repcopy as b where a.dm_ip =3D b.dm_ip and a.dm_user=3Db.dm_user and b.=
ss_key !=3D '') and report_status =3D 0 and dm_user =3D 'u1'; | 2010-04-13 =
18:20:02.828623+05:30


In such a case what can I do ?


--_000_BD69807DAE0CE44CA00A8338D0FDD08302CF97EA35oma00cexmbx 03_
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

osoft-com:office:office" xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:x=3D"urn:schemas-microsoft-com:office:excel" xmlns:p=3D"urn:schemas-m=
icrosoft-com:office:powerpoint" xmlns:a=3D"urn:schemas-microsoft-com:office=
:access" xmlns:dt=3D"uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:s=3D"=
uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:rs=3D"urn:schemas-microsof=
t-com:rowset" xmlns:z=3D"#RowsetSchema" xmlns:b=3D"urn:schemas-microsoft-co=
m:office:publisher" xmlns:ss=3D"urn:schemas-microsoft-com:office:spreadshee=
t" xmlns:c=3D"urn:schemas-microsoft-com:office:component:spread sheet" xmlns=
:odc=3D"urn:schemas-microsoft-com:office:odc" xmlns:oa=3D"urn:schemas-micro=
soft-com:office:activation" xmlns:html=3D"http://www.w3.org/TR/REC-html40" =
xmlns:q=3D"http://schemas.xmlsoap.org/soap/envelope/" xmlns:rtc=3D"http://m=
icrosoft.com/officenet/conferencing" xmlns:D=3D"DAV:" xmlns:Repl=3D"http://=
schemas.microsoft.com/repl/" xmlns:mt=3D"http://schemas.microsoft.com/share=
point/soap/meetings/" xmlns:x2=3D"http://schemas.microsoft.com/office/excel=
/2003/xml" xmlns:ppda=3D"http://www.passport.com/NameSpace.xsd" xmlns:ois=
=3D"http://schemas.microsoft.com/sharepoint/soap/ois/" xmlns:dir=3D"http://=
schemas.microsoft.com/sharepoint/soap/directory/" xmlns:ds=3D"http://www.w3=
..org/2000/09/xmldsig#" xmlns:dsp=3D"http://schemas.microsoft.com/sharepoint=
/dsp" xmlns:udc=3D"http://schemas.microsoft.com/data/udc" xmlns:xsd=3D"http=
://www.w3.org/2001/XMLSchema" xmlns:sub=3D"http://schemas.microsoft.com/sha=
repoint/soap/2002/1/alerts/" xmlns:ec=3D"http://www.w3.org/2001/04/xmlenc#"=
xmlns:sp=3D"http://schemas.microsoft.com/sharepoint/" xmlns:sps=3D"http://=
schemas.microsoft.com/sharepoint/soap/" xmlns:xsi=3D"http://www.w3.org/2001=
/XMLSchema-instance" xmlns:udcs=3D"http://schemas.microsoft.com/data/udc/so=
ap" xmlns:udcxf=3D"http://schemas.microsoft.com/data/udc/xmlfile " xmlns:udc=
p2p=3D"http://schemas.microsoft.com/data/udc/parttopart" xmlns:wf=3D"http:/=
/schemas.microsoft.com/sharepoint/soap/workflow/" xmlns:dsss=3D"http://sche=
mas.microsoft.com/office/2006/digsig-setup" xmlns:dssi=3D"http://schemas.mi=
crosoft.com/office/2006/digsig" xmlns:mdssi=3D"http://schemas.openxmlformat=
s.org/package/2006/digital-signature" xmlns:mver=3D"http://schemas.openxmlf=
ormats.org/markup-compatibility/2006" xmlns:m=3D"http://schemas.microsoft.c=
om/office/2004/12/omml" xmlns:mrels=3D"http://schemas.openxmlformats.org/pa=
ckage/2006/relationships" xmlns:spwp=3D"http://microsoft.com/sharepoint/web=
partpages" xmlns:ex12t=3D"http://schemas.microsoft.com/exchange/service s/20=
06/types" xmlns:ex12m=3D"http://schemas.microsoft.com/exchange/service s/200=
6/messages" xmlns:pptsl=3D"http://schemas.microsoft.com/sharepoint/soap/ Sli=
deLibrary/" xmlns:spsl=3D"http://microsoft.com/webservices/SharePointPor tal=
Server/PublishedLinksService" xmlns:Z=3D"urn:schemas-microsoft-com:" xmlns:=
st=3D"
" xmlns=3D"http://www.w3.org/TR/REC-html40">


>

Re: [GENERAL] Query is stuck

This is a multi-part message in MIME format.

------_=_NextPart_001_01CADB0A.C9034F66
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

select procpid, current_query,query_start - now(), backend_start

from pg_stat_activity

where current_query not like '%IDLE%' and waiting =3D 't';

=20

--

Thanks=20

Dhaval

=20

From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Satish Burnwal
(sburnwal)
Sent: Tuesday, April 13, 2010 2:04 PM
To: Plugge, Joe R.; pgsql-general@postgresql.org
Cc: pgsql-admin@postgresql.org
Subject: Re: [GENERAL] Query is stuck

=20

controlsmartdb=3D# select * from pg_stat_activity where waiting=3D't';

ERROR: column "waiting" does not exist

=20

From: Plugge, Joe R. [mailto:JRPlugge@west.com]=20
Sent: Tuesday, April 13, 2010 6:32 PM
To: Satish Burnwal (sburnwal); pgsql-general@postgresql.org
Cc: pgsql-admin@postgresql.org
Subject: RE: Query is stuck

=20

What do you get when you run this?

=20

select * from pg_stat_activity where waiting=3D't';

=20

=20

=20

From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Satish Burnwal
(sburnwal)
Sent: Tuesday, April 13, 2010 7:58 AM
To: pgsql-general@postgresql.org
Cc: pgsql-admin@postgresql.org
Subject: [ADMIN] Query is stuck

=20

I have a query which is not giving me the result even after 30 minutes.
I want to know how to detect what is going and what's wrong ?=20

=20

EXPLAIN query - gives me the following:

controlsmartdb=3D# explain select report_id, dm_ip, dm_mac, dm_user,
dm_os, report_time, sys_name, sys_user, sys_user_domain, ss_key,
login_time, role_id, new_vlan_id from repcopy as a where report_time =3D
(select max(report_time) from repcopy as b where a.dm_ip =3D b.dm_ip and
a.dm_user=3Db.dm_user and b.ss_key !=3D '') and report_status =3D 0 and
dm_user =3D 'u1';

QUERY PLAN

------------------------------------------------------------ ------------
----------------------------------------------------

Seq Scan on repcopy a (cost=3D0.00..1630178118.35 rows=3D35 width=3D133)

Filter: ((report_status =3D 0) AND ((dm_user)::text =3D 'u1'::text) AND
(report_time =3D (subplan)))

SubPlan

-> Aggregate (cost=3D8151.65..8151.66 rows=3D1 width=3D8)

-> Seq Scan on repcopy b (cost=3D0.00..8151.65 rows=3D1
width=3D8)

Filter: ((($0)::text =3D (dm_ip)::text) AND (($1)::text =
=3D
(dm_user)::text) AND ((ss_key)::text <> ''::text))

(6 rows)

=20

But EXPLAIN ANALYSE query hangs (is not giving me any output even after
30 minutes).

=20

Pg_stat_activity shows this - SELECT procpid, usename, current_query,
query_start from pg_stat_activity:

2942 | postgres | select report_id, dm_ip, dm_mac, dm_user, dm_os,
report_time, sys_name, sys_user, sys_user_domain, ss_key, login_time,
role_id, new_vlan_id from repcopy as a where report_time =3D (select
max(report_time) from repcopy as b where a.dm_ip =3D b.dm_ip and
a.dm_user=3Db.dm_user and b.ss_key !=3D '') and report_status =3D 0 and
dm_user =3D 'u1'; | 2010-04-13 18:20:02.828623+05:30

=20

=20

In such a case what can I do ?=20

=20

The information transmitted is intended only for the person or entity to wh=
ich it is addressed and may contain confidential and/or privileged material=
..=20
Any review, re-transmission, dissemination or other use of or taking of any=
action in reliance upon,this information by persons or entities other than=
the intended recipient is prohibited.=20
If you received this in error, please contact the sender and delete the mat=
erial from your computer.=20
Microland takes all reasonable steps to ensure that its electronic communic=
ations are free from viruses.=20
However, given Internet accessibility, the Company cannot accept liability =
for any virus introduced by this e-mail or any attachment and you are advis=
ed to use up-to-date virus checking software.=20

------_=_NextPart_001_01CADB0A.C9034F66
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

osoft-com:office:office" xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:x=3D"urn:schemas-microsoft-com:office:excel" xmlns:p=3D"urn:schemas-m=
icrosoft-com:office:powerpoint" xmlns:a=3D"urn:schemas-microsoft-com:office=
:access" xmlns:dt=3D"uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:s=3D"=
uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:rs=3D"urn:schemas-microsof=
t-com:rowset" xmlns:z=3D"#RowsetSchema" xmlns:b=3D"urn:schemas-microsoft-co=
m:office:publisher" xmlns:ss=3D"urn:schemas-microsoft-com:office:spreadshee=
t" xmlns:c=3D"urn:schemas-microsoft-com:office:component:spread sheet" xmlns=
:odc=3D"urn:schemas-microsoft-com:office:odc" xmlns:oa=3D"urn:schemas-micro=
soft-com:office:activation" xmlns:html=3D"http://www.w3.org/TR/REC-html40" =
xmlns:q=3D"http://schemas.xmlsoap.org/soap/envelope/" xmlns:rtc=3D"http://m=
icrosoft.com/officenet/conferencing" xmlns:D=3D"DAV:" xmlns:Repl=3D"http://=
schemas.microsoft.com/repl/" xmlns:mt=3D"http://schemas.microsoft.com/share=
point/soap/meetings/" xmlns:x2=3D"http://schemas.microsoft.com/office/excel=
/2003/xml" xmlns:ppda=3D"http://www.passport.com/NameSpace.xsd" xmlns:ois=
=3D"http://schemas.microsoft.com/sharepoint/soap/ois/" xmlns:dir=3D"http://=
schemas.microsoft.com/sharepoint/soap/directory/" xmlns:ds=3D"http://www.w3=
..org/2000/09/xmldsig#" xmlns:dsp=3D"http://schemas.microsoft.com/sharepoint=
/dsp" xmlns:udc=3D"http://schemas.microsoft.com/data/udc" xmlns:xsd=3D"http=
://www.w3.org/2001/XMLSchema" xmlns:sub=3D"http://schemas.microsoft.com/sha=
repoint/soap/2002/1/alerts/" xmlns:ec=3D"http://www.w3.org/2001/04/xmlenc#"=
xmlns:sp=3D"http://schemas.microsoft.com/sharepoint/" xmlns:sps=3D"http://=
schemas.microsoft.com/sharepoint/soap/" xmlns:xsi=3D"http://www.w3.org/2001=
/XMLSchema-instance" xmlns:udcs=3D"http://schemas.microsoft.com/data/udc/so=
ap" xmlns:udcxf=3D"http://schemas.microsoft.com/data/udc/xmlfile " xmlns:udc=
p2p=3D"http://schemas.microsoft.com/data/udc/parttopart" xmlns:wf=3D"http:/=
/schemas.microsoft.com/sharepoint/soap/workflow/" xmlns:dsss=3D"http://sche=
mas.microsoft.com/office/2006/digsig-setup" xmlns:dssi=3D"http://schemas.mi=
crosoft.com/office/2006/digsig" xmlns:mdssi=3D"http://schemas.openxmlformat=
s.org/package/2006/digital-signature" xmlns:mver=3D"http://schemas.openxmlf=
ormats.org/markup-compatibility/2006" xmlns:m=3D"http://schemas.microsoft.c=
om/office/2004/12/omml" xmlns:mrels=3D"http://schemas.openxmlformats.org/pa=
ckage/2006/relationships" xmlns:spwp=3D"http://microsoft.com/sharepoint/web=
partpages" xmlns:ex12t=3D"http://schemas.microsoft.com/exchange/service s/20=
06/types" xmlns:ex12m=3D"http://schemas.microsoft.com/exchange/service s/200=
6/messages" xmlns:pptsl=3D"http://schemas.microsoft.com/sharepoint/soap/ Sli=
deLibrary/" xmlns:spsl=3D"http://microsoft.com/webservices/SharePointPor tal=
Server/PublishedLinksService" xmlns:Z=3D"urn:schemas-microsoft-com:" xmlns:=
st=3D"
" xmlns=3D"http://www.w3.org/TR/REC-html40">

The information transmitted is intended only for the person or entity =

to which it is addressed and may contain confidential and/or privileged mat=

erial.=20

Any review, re-transmission, dissemination or other use of or taking of any=

 action in reliance upon,this information by persons or entities other than=

 the intended recipient is prohibited.=20

If you received this in error, please contact the sender and delete the mat=

erial from your computer.=20

Microland takes all reasonable steps to ensure that its electronic communic=

ations are free from viruses.=20

However, given Internet accessibility, the Company cannot accept liability =

for any virus introduced by this e-mail or any attachment and you are advis=

ed to use up-to-date virus checking software.=20

Re: [GENERAL] Query is stuck

In response to Szymon Guz :

> 2010/4/13 Satish Burnwal (sburnwal)
>=20
> > I have a query which is not giving me the result even after 30 minut=
es. I
> > want to know how to detect what is going and whatâ€=99s wrong ?
> >
> >
> >
> > EXPLAIN query - gives me the following:
> >
> > controlsmartdb=3D# explain select report_id, dm_ip, dm_mac, dm_user, =
dm_os,
> > report_time, sys_name, sys_user, sys_user_domain, ss_key, login_time=
,
> > role_id, new_vlan_id from repcopy as a where report_time =3D (select
> > max(report_time) from repcopy as b where a.dm_ip =3D b.dm_ip and
> > a.dm_user=3Db.dm_user and b.ss_key !=3D '') and report_status =3D 0 a=
nd dm_user =3D
> > 'u1';
> >
> > QUERY PLAN
> >
> >
> > ------------------------------------------------------------ ---------=
-------------------------------------------------------
> >
> > Seq Scan on repcopy a (cost=3D0.00..1630178118.35 rows=3D35 width=3D=
133)
> >
> > Filter: ((report_status =3D 0) AND ((dm_user)::text =3D 'u1'::text=
) AND
> > (report_time =3D (subplan)))
> >
> > SubPlan
> >
> > -> Aggregate (cost=3D8151.65..8151.66 rows=3D1 width=3D8)
> >
> > -> Seq Scan on repcopy b (cost=3D0.00..8151.65 rows=3D1 =
width=3D8)
> >
> > Filter: ((($0)::text =3D (dm_ip)::text) AND (($1)::t=
ext =3D
> > (dm_user)::text) AND ((ss_key)::text <> ''::text))
> >
> > (6 rows)
> >
> >
> >
> > But EXPLAIN ANALYSE query hangs (is not giving me any output even aft=
er 30
> > minutes).
> >
> >
> >
> > Pg_stat_activity shows this - SELECT procpid, usename, current_query,
> > query_start from pg_stat_activity:
> >
> > 2942 | postgres | select report_id, dm_ip, dm_mac, dm_user, dm_os,
> > report_time, sys_name, sys_user, sys_user_domain, ss_key, login_time=
,
> > role_id, new_vlan_id from repcopy as a where report_time =3D (select
> > max(report_time) from repcopy as b where a.dm_ip =3D b.dm_ip and
> > a.dm_user=3Db.dm_user and b.ss_key !=3D '') and report_status =3D 0 a=
nd dm_user =3D
> > 'u1'; | 2010-04-13 18:20:02.828623+05:30
> >
> >
> >
> >
> >
> > In such a case what can I do ?
> >
> >
> > First things that came to my mind:
>=20
> 1. Check if the query waits on some lock: add the column `waiting` to t=
he
> above query from pg_stat_activity.
> 2. Run vacuum analyze on the table repcopy

In addition to that, indexes on report_time, report_status, and dm_user
might help.

And your query is not "hung", it's just taking a LOOOOONG time. Based
on the explain, it could take several hours to complete. How many
rows are in repcopy? What is your vacuum schedule? Do a vacuum verbose,
if the number of dead rows is very high on that table, you may benefit
from doing a VACUUM FULL + REINDEX or CLUSTER on the table.

--=20
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [GENERAL] Query is stuck

On Tuesday 13 April 2010 6:03:43 am Satish Burnwal (sburnwal) wrote:
> controlsmartdb=3D# select * from pg_stat_activity where waiting=3D't';
>
> ERROR: column "waiting" does not exist
>
>

=46rom here:
http://www.postgresql.org/docs/8.4/interactive/monitoring-st ats.html#MONITO=
RING-STATS-VIEWS

My guess is you are being caught by this;

pg_stat_activity

"Furthermore, these columns are only visible if the user examining the view=
is a=20
superuser or the same as the user owning the process being reported on. '

--=20
Adrian Klaver
adrian.klaver@gmail.com

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Query is stuck

We can see from the result of EXPLAIN that your query is very costly to=20
execute (the important bit is "cost=3D0.00..1630178118.35"). The fact=20
that it is doing a sequential scan ("Seq Scan") tells us why it takes so=20
long.

Without being able to see your data, it is hard to offer suggestions=20
about how you could improve your query. But one thing that jumps out at=20
me is that you have a call to the max() function in your WHERE clause.=20
In my experience, having a function call in a WHERE clause is very=20
expensive to execute. I think you would do best if you can find a=20
different way to build your query that avoids this handicap. Maybe a=20
new index on your table would help too, perhaps a partial index; but=20
again, this is just guessing without knowing the nature of your data.

Thank you,

Lewis Kapell
Computer Operations
Seton Home Study School



On 4/13/2010 8:58 AM, Satish Burnwal (sburnwal) wrote:
> I have a query which is not giving me the result even after 30 minutes.
> I want to know how to detect what is going and what=92s wrong ?
>
> EXPLAIN query - gives me the following:
>
> controlsmartdb=3D# explain select report_id, dm_ip, dm_mac, dm_user,
> dm_os, report_time, sys_name, sys_user, sys_user_domain, ss_key,
> login_time, role_id, new_vlan_id from repcopy as a where report_time =3D
> (select max(report_time) from repcopy as b where a.dm_ip =3D b.dm_ip an=
d
> a.dm_user=3Db.dm_user and b.ss_key !=3D '') and report_status =3D 0 and
> dm_user =3D 'u1';
>
> QUERY PLAN
>
> ------------------------------------------------------------ -----------=
-----------------------------------------------------
>
> Seq Scan on repcopy a (cost=3D0.00..1630178118.35 rows=3D35 width=3D133=
)
>
> Filter: ((report_status =3D 0) AND ((dm_user)::text =3D 'u1'::text) AND
> (report_time =3D (subplan)))
>
> SubPlan
>
> -> Aggregate (cost=3D8151.65..8151.66 rows=3D1 width=3D8)
>
> -> Seq Scan on repcopy b (cost=3D0.00..8151.65 rows=3D1 width=3D8)
>
> Filter: ((($0)::text =3D (dm_ip)::text) AND (($1)::text =3D (dm_user)::=
text)
> AND ((ss_key)::text <> ''::text))
>
> (6 rows)
>
> But EXPLAIN ANALYSE query hangs (is not giving me any output even after
> 30 minutes).
>
> Pg_stat_activity shows this - SELECT procpid, usename, current_query,
> query_start from pg_stat_activity:
>
> 2942 | postgres | select report_id, dm_ip, dm_mac, dm_user, dm_os,
> report_time, sys_name, sys_user, sys_user_domain, ss_key, login_time,
> role_id, new_vlan_id from repcopy as a where report_time =3D (select
> max(report_time) from repcopy as b where a.dm_ip =3D b.dm_ip and
> a.dm_user=3Db.dm_user and b.ss_key !=3D '') and report_status =3D 0 and
> dm_user =3D 'u1'; | 2010-04-13 18:20:02.828623+05:30
>
> In such a case what can I do ?
>


--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Query is stuck

Lewis Kapell wrote:

> The fact that it is doing a sequential scan ("Seq Scan") tells us
> why it takes so long.

Well, that and the fact that for each row in one scan of the table,
it scans the entire table again. :-(

>> select report_id, dm_ip, dm_mac, dm_user, dm_os, report_time,
>> sys_name, sys_user, sys_user_domain, ss_key, login_time, role_id,
>> new_vlan_id from repcopy as a where report_time = (select
>> max(report_time) from repcopy as b where a.dm_ip = b.dm_ip and
>> a.dm_user=b.dm_user and b.ss_key != '') and report_status = 0 and
>> dm_user = 'u1';

I *think* that's equivalent to the following, which might be faster:

select
report_id, a.dm_ip, dm_mac, dm_user, dm_os, a.report_time,
sys_name, sys_user, sys_user_domain, ss_key, login_time,
role_id, new_vlan_id
from repcopy a
join (
select dm_ip, max(report_time) as report_time
from repcopy
where b.ss_key != '' and b.dm_user = 'u1'
group by dm_ip
) b
on (b.dm_ip = a.dm_ip and b.report_time = a.report_time)
where a.report_status = 0 and a.dm_user = 'u1'
;

-Kevin

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin